package com.rsc.learningbar.dao;

import com.rsc.learningbar.entity.StudentPlan;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;


@Repository
public interface StudentPlanRepository extends JpaRepository<StudentPlan, Integer> {
    //    //自定义repository。手写sql
//    @Query(value = "update t_route set uri=?1 where id=?2",nativeQuery = true)   //占位符传值形式
//    @Modifying
//    int updateById(String name,int id);
//


    //SPEL表达式
    @Query(value = "select u.reminded_time, u.user_id,u.id, u.create_time, u.memory_type, u.remind_status,u.remind_time, u.student_mission_id,u.finish_time ,m.student_mission_name,u.point  from student_plan u " +
            "left join student_mission m " +
            "on u.student_mission_id = m.id " +
            "where u.user_id=:userId and u.student_mission_id=:missionId and u.point is null order by u.student_mission_id,u.remind_time desc", nativeQuery = true)
    List<StudentPlan> findStudentPlan(@Param("userId") Long userId,@Param("missionId") int missionId);

    //SPEL表达式
    @Query(value = "select u.reminded_time, u.user_id,u.id, u.create_time, u.memory_type, u.remind_status,u.remind_time, u.student_mission_id,u.finish_time ,m.student_mission_name,u.point  from student_plan u " +
            "left join student_mission m " +
            "on u.student_mission_id = m.id " +
            "where u.user_id=:userId and u.point is null and DATE_FORMAT(u.remind_time,'%Y%m%d')=:date order by u.student_mission_id,u.remind_time", nativeQuery = true)
    List<StudentPlan> findStudentPlan(@Param("userId") Long userId, @Param("date") String date);

    //SPEL表达式u
    @Query(value = "select u.reminded_time, u.user_id,u.id, u.create_time, u.memory_type, u.remind_status,u.remind_time, u.student_mission_id,u.finish_time ,m.student_mission_name,u.point from student_plan u " +
            "left join student_mission m " +
            "on u.student_mission_id = m.id " +
            "where u.user_id=:userId and u.point is not null order by u.student_mission_id,u.finish_time", nativeQuery = true)
    List<StudentPlan> findStudentResult(@Param("userId") Long userId);

    //SPEL表达式u
    @Query(value = "select u.reminded_time, u.user_id,u.id, u.create_time, u.memory_type, u.remind_status,u.remind_time, u.student_mission_id,u.finish_time ,m.student_mission_name,u.point from student_plan u " +
            "left join student_mission m " +
            "on u.student_mission_id = m.id " +
            "where u.user_id=:userId and u.point is null order by u.student_mission_id,u.remind_time", nativeQuery = true)
    List<StudentPlan> findTodoPlanByUserId(@Param("userId") Long userId);

    //SPEL表达式u
    @Query(value = "select u.reminded_time, u.user_id,u.id, u.create_time, u.memory_type, u.remind_status,u.remind_time, u.student_mission_id,u.finish_time ,m.student_mission_name,u.point from student_plan u " +
            "left join student_mission m " +
            "on u.student_mission_id = m.id " +
            "where u.point is null order by u.student_mission_id,u.remind_time", nativeQuery = true)
    List<StudentPlan> findTodoPlan();
}
